--SELECT * FROM cat_applicant
--DELETE FROM cat_applicant
INSERT INTO cat_applicant
SELECT 
case len(convert(varchar(10),a.id))
    when 7 then '00000000-0000-0000-0000-00000' + convert(varchar(10),a.id)
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),a.id)
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),a.id) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),a.id) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),a.id) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),a.id) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),a.id) 
    end As Id,case len(convert(varchar(10),jobid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),jobid) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),jobid) end  AS [JobId],JobName AS [JobTitle],case len(convert(varchar(10),jobid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),jobid) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),jobid) end  AS [JobPostingId],case len(convert(varchar(10),a.companyid))    
	when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),a.companyid) 	
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),a.companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),a.companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),a.companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),a.companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),a.companyid) 
    end AS [CompanyId],dbo.getcompanyname(a.companyid) AS [CompanyName],dbo.getlocation(jobprovince,jobcity) AS [Location],'00000000-0000-0000-0000-00000' + convert(varchar(7),resumeid) AS  [ResumeId],JobletterContent AS [CoverLetter],NULL AS [DesiredSalary],dateadd("day",-30,enddate) AS [StartDate],j.Enddate AS [EndDate],a.CreatedDate AS [DateApplied],dbo.getuserid(resumeid) AS [JobseekerId],1 AS [Source],case Repplyflagwhen 1 then 1else 2 end AS [Status]FROM srv_lnk.nchr2008.dbo.ReciveResume a left join srv_lnk.nchr2008.dbo.companyjob j
on a.jobid = j.id
WHERE resumeid in (select right(id,7) from cv_resume) and a.createddate >'2012-1-1'

--select top 10 * from srv_lnk.nchr2008.dbo.companyjob



--SELECT TOP 10 * FROM srv_lnk.nchr2008.dbo.ReciveResume
